/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package me.zhengjie.business.repository;

import me.zhengjie.business.domain.KC21;
import me.zhengjie.business.domain.vo.Kc21Vo;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
 * 照护保险登记信息
* @author xiahongbo
* @date 2023-05-08
**/
public interface Kc21Repository extends JpaRepository<KC21, String>, JpaSpecificationExecutor<KC21> {


    /**
     * 查询照护保险登记信息
     * @param akb020
     * @param akc190
     * @param aac008
     * @param aab034
     * @param ryxx
     * @param pageable
     * @return
     */
    @Query(value = "select distinct new me.zhengjie.business.domain.vo.Kc21Vo( " +
            "r1.aac001, " +
            "r1.aac003, " +
            "r2.bisZhsqSnrySfz, " +
            "r2.bisZhsqSnryYbssqx, " +
            "r1.aab004, " +
            "r1.aka130, " +
            "r1.akb020, " +
            "r1.akb021, " +
            "r1.akc192, " +
            "r1.akc194, " +
            "r1.ckc119, " +
            "r1.aae036Js, " +
            "r1.akc190, " +
            "r1.aae036, " +
            "r1.aac008) " +
            "from KC21 r1, Zhsq r2 " +
            " where  1=1 " +
            "   and r1.aac001 = r2.bisZhsqGrbh " +
            "   and (?1 IS NULL OR ?1 = '' OR r1.akb020 = ?1 ) " +
            "   and (?2 IS NULL OR ?2 = '' OR r1.akc190 = ?2 ) " +
            "   and (?3 IS NULL OR ?3 = '' OR r1.aac008 = ?3 ) " +
            "   and (?4 IS NULL OR ?4 = '' OR r2.bisZhsqSnryYbssqx = ?4 ) " +
            "   and (?5 IS NULL OR ?5 = '' OR r1.aac001 = ?5 OR r1.aac003 like '%'||?5||'%' OR r2.bisZhsqSnrySfz like '%'||?5||'%' ) ",
            countQuery = "select count(*) from KC21 r1, Zhsq r2 " +
                    " where 1=1 " +
                    "   and r1.aac001 = r2.bisZhsqGrbh " +
                    "   and (?1 IS NULL OR ?1 = '' OR r1.akb020 = ?1 ) " +
                    "   and (?2 IS NULL OR ?2 = '' OR r1.akc190 = ?2 ) " +
                    "   and (?3 IS NULL OR ?3 = '' OR r1.aac008 = ?3 ) " +
                    "   and (?4 IS NULL OR ?4 = '' OR r2.bisZhsqSnryYbssqx = ?4 ) " +
                    "   and (?5 IS NULL OR ?5 = '' OR r1.aac001 = ?5 OR r1.aac003 like '%'||?5||'%' OR r2.bisZhsqSnrySfz like '%'||?5||'%' ) ")
    Page<Kc21Vo> queryKc21VoPageList(String akb020, String akc190, String aac008, String aab034 , String ryxx,  Pageable pageable);

    /**
     * 查询照护保险登记信息，导出
     * @param akb020
     * @param akc190
     * @param aac008
     * @param aab034
     * @param ryxx
     * @return
     */
    @Query(value = "select new me.zhengjie.business.domain.vo.Kc21Vo( " +
            "r1.aac001, " +
            "r1.aac003, " +
            "r2.bisZhsqSnrySfz, " +
            "r2.bisZhsqSnryYbssqx, " +
            "r1.aab004, " +
            "r1.aka130, " +
            "r1.akb020, " +
            "r1.akb021, " +
            "r1.akc192, " +
            "r1.akc194, " +
            "r1.ckc119, " +
            "r1.aae036Js, " +
            "r1.akc190, " +
            "r1.aae036, " +
            "r1.aac008) " +
            "from KC21 r1, Zhsq r2 " +
            " where  1=1 " +
            "   and r1.aac001 = r2.bisZhsqGrbh " +
            "   and (?1 IS NULL OR ?1 = '' OR r1.akb020 = ?1 ) " +
            "   and (?2 IS NULL OR ?2 = '' OR r1.akc190 = ?2 ) " +
            "   and (?3 IS NULL OR ?3 = '' OR r1.aac008 = ?3 ) " +
            "   and (?4 IS NULL OR ?4 = '' OR r2.bisZhsqSnryYbssqx = ?4 ) " +
            "   and (?5 IS NULL OR ?5 = '' OR r1.aac001 = ?5 OR r1.aac003 like '%'||?5||'%' OR r2.bisZhsqSnrySfz like '%'||?5||'%' ) ")
    List<Kc21Vo> queryKc21VoAllList(String akb020, String akc190, String aac008, String aab034 , String ryxx);
}